const connection = require('../../sql');
const excel = require('../../middleware/excel');
class ClientService {
  async createClient({
    cName,
    remark,
    state,
    orgName,
    taxNum,
    bankName,
    bankCard,
    billInfo,
    email,
    address,
    phone,
    creater_id,
    updata_id
  }) {
    try {
      const now = new Date();
      const sql = `
        INSERT INTO basic_client (
          cName, remark, state, orgName, taxNum, bankName, bankCard,
          billInfo, email, address, phone, creater_id, updata_id,
          creater_at, updata_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      const [result] = await connection.execute(sql, [
        cName || null,
        remark || null,
        state !== undefined ? state : null,
        orgName || null,
        taxNum || null,
        bankName || null,
        bankCard || null,
        billInfo || null,
        email || null,
        address || null,
        phone || null,
        creater_id,
        updata_id,
        now,
        now
      ]);
      return { id: result.insertId };
    } catch (error) {
      throw error;
    }
  }

  async deleteClients(ids) {
    try {
      const sql = `
        DELETE FROM basic_client
        WHERE id IN (${ids.map(() => '?').join(',')})
      `;
      const [result] = await connection.execute(sql, ids);
      if (result.affectedRows === 0) {
        throw new Error('没有可删除的客户');
      }

      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async updateClient({
    id,
    cName,
    remark,
    state,
    orgName,
    taxNum,
    bankName,
    bankCard,
    billInfo,
    email,
    address,
    phone,
    updata_id
  }) {
    try {
      const sql = `
        UPDATE basic_client
        SET
          cName = ?, remark = ?, state = ?, orgName = ?,
          taxNum = ?, bankName = ?, bankCard = ?,
          billInfo = ?, email = ?, address = ?,
          phone = ?, updata_id = ?, updata_at = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [
        cName || null,
        remark || null,
        state !== undefined ? state : null,
        orgName || null,
        taxNum || null,
        bankName || null,
        bankCard || null,
        billInfo || null,
        email || null,
        address || null,
        phone || null,
        updata_id,
        now,
        id
      ]);
      if (result.affectedRows === 0) {
        throw new Error('客户不存在');
      }

      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async getClientList({ page, limit, cName, state, phone }) {
    const offset = (page - 1) * limit;
    let whereClause = '';
    const params = [];

    if (cName) {
      whereClause += ' AND c.cName LIKE ?';
      params.push(`%${cName}%`);
    }
    if (state !== undefined) {
      whereClause += ' AND c.state = ?';
      params.push(state);
    }
    if (phone) {
      whereClause += ' AND c.phone LIKE ?';
      params.push(`%${phone}%`);
    }

    const countSql = `
      SELECT COUNT(*) as count
      FROM basic_client c
      WHERE 1=1 ${whereClause}
    `;
    const listSql = `
      SELECT 
        c.id, c.cName, c.remark, c.state, c.orgName, c.taxNum,
        c.bankName, c.bankCard, c.billInfo, c.email, c.address,
        c.phone, c.creater_id, c.updata_id,
        c.creater_at, c.updata_at,
        creator.username AS creater_username,
        updater.username AS updata_username
      FROM basic_client c
      LEFT JOIN sys_user creator ON c.creater_id = creator.id AND creator.del_flag = 'F'
      LEFT JOIN sys_user updater ON c.updata_id = updater.id AND updater.del_flag = 'F'
      WHERE 1=1 ${whereClause}
      ORDER BY c.creater_at DESC
      LIMIT ? OFFSET ?
    `;

    try {
      const [countResult] = await connection.execute(countSql, params);
      const [rows] = await connection.execute(listSql, [...params, String(limit), String(offset)]);

      return {
        current: page,
        limit,
        totalPage: Math.ceil(countResult[0].count / limit) || 1,
        total: countResult[0].count,
        rows
      };
    } catch (error) {
      throw error;
    }
  }

  async updateClientState({ id, newState, updata_id }) {
    try {
      const sql = `
        UPDATE basic_client
        SET state = ?, updata_id = ?, updata_at = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [newState, updata_id, now, id]);
      if (result.affectedRows === 0) {
        throw new Error('没有可更新的客户');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async exportClient(ctx, { cName, state, phone }) {
    let whereClause = '';
    const params = [];
    if (cName) {
      whereClause += ' AND c.cName LIKE ?';
      params.push(`%${cName}%`);
    }
    if (state !== undefined) {
      whereClause += ' AND c.state = ?';
      params.push(state);
    }
    if (phone) {
      whereClause += ' AND c.phone LIKE ?';
      params.push(`%${phone}%`);
    }
    const listSql = `
      SELECT 
        c.cName, c.remark, c.state, c.orgName, c.taxNum,
        c.bankName, c.bankCard, c.billInfo, c.email, c.address,
        c.phone
      FROM basic_client c
      WHERE 1=1 ${whereClause}
      ORDER BY c.creater_at DESC
    `;

    try {
      const headerMap = {
        cName: '客户名称',
        remark: '备注',
        state: '状态',
        orgName: '组织名称',
        taxNum: '税号',
        bankName: '开户银行',
        bankCard: '银行账号',
        billInfo: '发票信息',
        email: '邮箱',
        address: '地址',
        phone: '电话'
      };
      const options = {
        headerMap,
        headers: Object.values(headerMap),
        fileName: '客户数据'
      };
      try {
        await excel.exportExcelMiddleware(ctx, listSql, params, options);
      } catch (error) {
        console.error('Error exporting Excel:', error);
      }
    } catch (error) {
      throw error;
    }
  }
}

module.exports = new ClientService();
